import java.io.File;
import java.io.IOException;
import java.util.*;
import java.util.regex.*;
import java.util.List;


import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.WritableWorkbook;
import jxl.read.biff.BiffException;

import javax.swing.plaf.synth.SynthUI;


public class load {
    public static void main(String[] args) throws BiffException,IOException,WriteException
    {
        Set<String> set = new LinkedHashSet<String>();
        Map<String,Integer> map = new HashMap<String,Integer>();

        List<String> listLast = new ArrayList<String>();

        File xlsFile = new File("高速公路.xls");
        // 获得工作簿对象
        Workbook workbook = Workbook.getWorkbook(xlsFile);
        // 获得所有工作表
        Sheet[] sheets = workbook.getSheets();
        // 遍历工作表
        if (sheets != null)
        {
            for (Sheet sheet : sheets)
            {
                // 定义rows用来获得行数
                int rows = sheet.getRows();
                // 定义cols用来获得列数
                int cols = sheet.getColumns();
                // 读取数据
                for (int row = 0; row < rows; row++)
                {
                    for (int col = 0; col < cols; col++)
                    {
                        String s = sheet.getCell(col, row).getContents();
                        if(!s.equals("")) {
                            listLast.add(s);
                        }else {
                            listLast.add(" ");
                        }

                        Pattern pattern = Pattern.compile("[0-9]{1,}");
                        Matcher matcher = pattern.matcher(s);
                        if(!matcher.matches() && !s.equals("")) {
                            set.add(s);
                        }

                    }
                    //System.out.println();
                }
            }
        }
        workbook.close();

        int setSize = set.size();

        List<String> list = new ArrayList<>(set);
        for(int i = 0; i < setSize; i++ ) {
            map.put(list.get(i), i+1);
        }
        System.out.println(map);
        System.out.println(map.size());



        //写数据

        File xlsFile0 = new File("jxl.xls");
        // 创建一个工作簿
        WritableWorkbook workbook0 = Workbook.createWorkbook(xlsFile0);
        // 创建一个工作表
        WritableSheet sheet0 = workbook0.createSheet("sheet1", 0);
        int row = 0, column = 0;
        System.out.println(listLast);
        System.out.println(listLast.size());

        int j = 0;
        int t = listLast.size();
        for(int i = 0; i < t; ) {
            if(j == t)
                break;
            String s1 = listLast.get(j);
            if(s1.equals(" ")) {
                j++;
                continue;
            }
            String s2 = listLast.get(j);
            if(s2.equals(" ")) {
                j++;
                continue;
            }
            String s3 = listLast.get(j);
            if(s3.equals(" ")) {
                j++;
                continue;
            }
            i++;
            //获取地名对应的序号
            int s1Num = map.get(s1);
            int s3Num = map.get(s3);

            sheet0.addCell(new Label(column++, row, s1Num + ""));
            sheet0.addCell(new Label(column++, row, s2));
            sheet0.addCell(new Label(column, row, s3Num + ""));
            j += 2;
            column = 0;
            row += 1;
        }
        //System.out.println(t+" "+j);
        workbook0.write();
        workbook0.close();



        File xlsFile1 = new File("地点与对应的序号.xls");
        WritableWorkbook workbook1 = Workbook.createWorkbook(xlsFile1);
        WritableSheet sheet1 = workbook1.createSheet("sheet1", 0);
        j = 0;
        for(int i = 0; j < t; j++,i++) {
            String s = listLast.get(i);
            if(s.equals(" "))
                continue;

            Pattern pattern = Pattern.compile("[0-9]{1,}");
            Matcher matcher = pattern.matcher(s);
            if(!matcher.matches()) {
                sheet1.addCell(new Label(0, i, s));
                sheet1.addCell(new Label(1, i, map.get(s)+""));
            }
        }
        workbook1.write();
        workbook1.close();



    }
}

